1. Field of the Invention
The invention relates generally to database systems of the kind which store metadata in catalogs or data dictionaries and more specifically to techniques for reading and manipulating the metadata.
2. Description of Related Art
A preferred embodiment of the invention is implemented in an improved version of the Oracle10g™ Server, manufactured by Oracle Corporation, Redwood City, Calif. The Oracle10g server includes an object-relational database system. The object-relational database system appears in FIG. 1 as DBMS 103; prior to modification as required for the invention, DBMS 103 includes database 108, together with those tables 107 in data dictionary 106 that are required to implement database 108.
Information is stored in DBMS 103 as objects. In FIG. 1, all objects have the reference number 105. Objects are divided into classes according to the kind of information they contain. Shown in FIG. 1 are objects of the TABLE class 141, the TRIGGER class 143, the INDEX class 145, the UDT (user-defined type) class 147, and the VIEW class 149. Many other classes of objects exist as well in DBMS 103. Object classes that are of particular importance for the present discussion are TABLE class 141, UDT class 147, and VIEW class 149.
DBMS 103 is a relational database system. In such systems, most of the data is stored in objects of TABLE class 141. As implied by the name, an object of TABLE class 141 contains a table. The table is made up of rows and columns. Each row has a field for each of the columns. A column specifies a kind of data value and a row represents an entity which has values of the kinds specified by the column. For example, in a table for storing personal information, the columns might be called last_name, first_name, m_i, street_addr, and so forth, with each row representing a person and the fields in the row having the appropriate values for that person.
DBMS 103 is an object relational database system. In such a system, the data values in a column may be objects which have user-defined types or UDTs. For instance, a user might define a type called contact_info which contained fields for all of the contact information for an individual and a column in a table of persons might specify a field for which the values are objects of type contact_info. Objects of class UDT are objects with user-defined types.
The objects of the TABLE class are what is termed base tables, that is, the information contained in an object of the TABLE class is stored as a table in DBMS 103's memory system. DBMS 103 has other tables which use the information contained in one or more base tables, but do not make separate copies of the information. These tables are termed views and the objects that represent views are objects of VIEW class 149. Views can be used to combine information from a number of different base tables and even other views. A kind of view which is of particular interest in the present discussion is an object view. An object view is a view that is associated with a user-defined type. Each row in the table specified by the object view is an object of the user-defined type associated with the object view. The information which the object view obtains from the base tables either directly or via other views is mapped into fields of the objects which make up the rows in the view.
The objects in DBMS 103 belong either to data dictionary 106 or to database 108. The information in data dictionary 106 is metadata, that is, information that defines all of the objects in DBMS 103, those in database 108, and also those in data dictionary 106. In the Oracle8 server, most of the objects in data dictionary 106 are TABLE objects 105 that belong to system tables 107. For example, there is a table named tab$ in system tables 107 that contains a row for every table defined in DBMS 103, including the tables in system tables 107.
U.S. Pat. No. 6,708,186 and U.S. Ser. No. 10/014,038 disclose a system which is termed herein the Metadata API. The Metadata API is a central facility for extracting and manipulating complete representations of the metadata for database objects. It allows one to                fetch an object's metadata as XML        transform the XML in a variety of ways, including transforming it into SQL DDL        submit the XML to recreate the object        
The Metadata API implements an object type using three entities:                A user-defined type (UDT) whose attributes comprise all the metadata for objects of the type. An object's XML representation is a translation of a type instance into XML, with the XML tag names derived from the type attribute names. (In the case of TABLEs, several UDTs are needed to represent the different varieties of the object type.)        An object view of the UDT which populates instances of the object type.        An XSL script that converts the XML representation of an object into SQL DDL.Homogeneous Object Types        
For the purposes of this API every entity in the database is modeled as an object which belongs to an object type. E.g., the table SCOTT.EMP is an object; its object type is TABLE. When you fetch an object's metadata you must specify the object type.
Object types can be grouped according to their attributes. Schema objects (e.g., tables) belong to schemas. Named objects have unique names; if they are schema objects, the name is unique in the schema. Dependent objects (e.g., indexes) are defined with reference to a base object. Granted objects are granted or assigned to a user or role and therefore have a named grantee.
In order to fetch a particular object or set of objects within an object type, you specify a filter. Different filters are defined for each object type. e.g., two of the filters defined for the TABLE object type are SCHEMA and NAME; they allow you to say that you want the table whose schema is SCOTT and whose name is EMP.
The metadata is returned in an XML document. You can use the API to specify one or more transforms (XSLT scripts) to be applied to the XML, either when the metadata is fetched (“FETCH_xxx”), when it is resubmitted (“PUT”), or simply as a single stand-alone operation (“CONVERT”). The API provides some predefined transforms including one named “DDL” which transforms the XML document into SQL creation DDL. Since the XSLT language permits conditional transformation based on input parameters, you can specify transform parameters for the transforms you have added. e.g., if you have added the DDL transform for a TABLE object you can specify the TABLESPACE transform parameter to indicate whether you want tablespace information to appear in the DDL. remap parameters are a variant of this idea: they allow you to modify an object by changing specific old attribute values to new values. E.g., you can use the REMAP_SCHEMA parameter to change occurrences of schema name SCOTT in a document to schema name BLAKE.
It is often desirable to access specific attributes of an object's metadata, e.g., its name or schema. You could get this information by parsing the returned metadata, but the API provides another mechanism: you can specify parse items, specific attributes that will be parsed out of the metadata and returned in a separate data structure.
The Metadata API provides two styles of retrieval interface, one for programmatic use, the other for ad hoc browsing.
Heterogeneous Object Types
Some users of the Metadata API need to fetch collections of objects which, although they are of different object types, nevertheless comprise a logical unit, e.g., all the objects in a database, or all objects in a schema, or a table and all its dependent indexes, constraints, grants, audits, etc. To meet this need the API provides, in addition to the homogeneous object types discussed so far, a number of heterogeneous object types. A heterogeneous object type is an ordered set of object types. Heterogeneous object types have various uses including                implementing collections of objects corresponding to the Data Pump Export modes (full database, schema, user, tablespace)        implementing object types that are themselves object collections (e.g., DOMAIN_INDEX).        
The member types of a heterogeneous object type may be homogeneous or heterogeneous; the result is a tree of object types. Each node in the tree has one or more path names. A node's path name is the node's name, optionally prefixed by the names of its ancestors, separated by slashes. For example, consider the following heterogeneous object type:
EXAMPLE  TABLE    TABLE    OBJECT_GRANT  VIEW    VIEW    OBJECT_GRANT
The top-level type ‘EXAMPLE’ contains two member heterogeneous object types named ‘TABLE’ and ‘VIEW’. The heterogeneous type ‘TABLE’ contains two homogeneous types ‘TABLE’ and ‘OBJECT_GRANT’. The path names for this last object are ‘OBJECT_GRANT’, ‘TABLE/OBJECT_GRANT’ and ‘EXAMPLE/TABLE/OBJECT_GRANT’. Note that the partial path name ‘OBJECT_GRANT’ denotes two nodes (grants on tables and grants on views). Only the full path name is guaranteed to uniquely identify a single node. The API interprets a path name as meaning the named node (or nodes) and all of its (their) descendants. Thus, ‘EXAMPLE/TABLE’ denotes the entire heterogeneous ‘TABLE’ object type (both tables and grants on tables).
Heterogeneous object types can take filters. A filter on the type is translated into filters on the member types. In the example above, a NAME filter on the TABLE heterogeneous object type translates to (a) a NAME filter on the homogeneous TABLE member type and (b) a BASE_OBJECT_NAME filter on the OBJECT_GRANT member type. The result is what you would expect: the API fetches the named table and its grants.
The Submit Interface
The API provides both a retrieval and a submit interface. You use the submit interface to recreate an object extracted by the retrieval interface. When multiple objects are recreated, however, the order in which they are created is constrained. E.g., a schema (user) must be created before its objects. The order is only a partial order; some objects need not follow any particular creation order. E.g., one schema and its objects may be created before, after or concurrently with another schema and its objects so long as there are no cross-schema dependencies. Thus for a given collection of objects there may be many valid creation orders (and many invalid ones). In general, the Metadata API returns objects belonging to a heterogeneous object type in a valid creation order.
Problems with the Metadata API
The Metadata API as disclosed in U.S. Pat. No. 6,708,186 and U.S. Ser. No. 10/014,038 produced full XML (the XML produced by GET_XML, etc.). Full XML is a collection of data values in the same format as they appear in the Oracle dictionary in a particular Oracle RDBMS, e.g., bit-encoded columns like “property” and “flags” are fetched “as is” rather than being exploded into separate elements. The fact that full XML uses the format in which data values appear in the same format as they do in the Oracle dictionary in a particular Oracle RDBMS causes a number of problems:                Full XML is far more difficult for a human reader to understand than the DDL statements which created the objects described in the metadata.        Full XML contains many elements of the metadata that cannot be specified by the user.        Much of the information in full XML is dependent on the manner in which the database is implemented in a particular database system; consequently, a full XML representation of metadata from one system cannot be compared with a full XML representation of metadata from another system, even though the metadata from the systems is logically equivalent.        
The fact that the full XML representation of metadata from a particular system is peculiar to that system means that full XML from two different Oracle RDBMS systems cannot be automatically compared to determine the differences between the metadata in the systems, and that means in turn that the differences between the full XML from the systems cannot be used to automatically alter the metadata in one of the systems so that it is identical to the metadata in the other of the systems. It is an object of the invention disclosed herein to overcome these problems with full XML by providing a representation of a relational database management system's metadata that is easily read by humans, contains only user-specifiable elements of the metadata, and is comparable from one relational database management system to another.